﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;

namespace NET.Library.Data.Sql
{
    /// <summary>
    /// 
    /// </summary>
    public sealed class DBSQL: DB
    {
        //#region Fields

        //#endregion

        #region Properties
        //public string ConnectionString
        //{
        //    get;
        //    set;
        //}
        #endregion

        #region Constructors
        public DBSQL() { }

        public DBSQL(string connectionString) 
        {
            this.ConnectionString=connectionString;
        }
        #endregion

        #region Instance Methods

        #region DataReader
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public SqlDataReader DBExecuteSqlReader(string storedProcedureName)
        {
            return (SqlDataReader)base.DBExecuteReader(storedProcedureName); //(SqlDataReader)DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName);
        }

        public SqlDataReader DBSQLExecuteSqlReader(string storedProcedureName)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            return (SqlDataReader)db.ExecuteReader(storedProcedureName);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
        public SqlDataReader DBExecuteSqlReader(string storedProcedureName, params object[] parameterValues)
        {
            return (SqlDataReader)base.DBExecuteScalar(storedProcedureName, parameterValues); 
            //return (SqlDataReader)DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName, parameterValues);
        }

        public SqlDataReader DBSQLExecuteSqlReader(string storedProcedureName, params object[] parameterValues)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            return (SqlDataReader)db.ExecuteReader(storedProcedureName, parameterValues);
        }
        #endregion

        #region DataSet

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and returns the results as a DataSet.
        /// </summary>
        /// <param name="sqlCommand">The SqlCommand object to use.</param>
        /// <returns>Result as a DataSet.</returns>
        ///----------------------------------------------------------------------------------
        public DataSet DBExecuteDataSet(SqlCommand sqlCommand)
        {
            return base.DBExecuteDataSet(sqlCommand);
            //Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

            //try
            //{
            //    return db.ExecuteDataSet(sqlCommand);
            //}
            //finally
            //{
            //    sqlCommand.Connection.Close();
            //    sqlCommand.Dispose();
            //    if (db != null) db = null;
            //}
        }

        public DataSet DBSQLExecuteDataSet(SqlCommand sqlCommand)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            try
            {
                return db.ExecuteDataSet(sqlCommand);
            }
            finally
            {
                if (sqlCommand.Connection.State != ConnectionState.Closed) { sqlCommand.Connection.Close(); }
            }
        }

        public DataSet DBSQLExecuteDataSet(string storedProcedureName)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            try
            {
                return db.ExecuteDataSet(CommandType.StoredProcedure, storedProcedureName);
            }
            finally
            {
                //if (sqlCommand.Connection.State != ConnectionState.Closed) { sqlCommand.Connection.Close(); }
                
            }
        }

        public DataSet DBSQLExecuteDataSet(string storedProcedureName, params object[] parameterValues)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            try
            {
                return db.ExecuteDataSet(storedProcedureName, parameterValues);
            }
            finally
            {
               // if (sqlCommand.Connection.State != ConnectionState.Closed) { sqlCommand.Connection.Close(); }
            }
        }



        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// 
        ///////// </summary>
        ///////// <param name="storedProcName">The name of the insert stored procedure to execute.</param>
        ///////// <param name="dsProcParams">A dataset of parameters to pass to the stored procedure.</param>
        ///////// <returns></returns>
        /////////----------------------------------------------------------------------------------
        //////public DataSet DBExecuteStoredProc(string storedProcName, DataSet dsProcParams)
        //////{
        //////    return base.DBExecuteStoredProc(storedProcName, dsProcParams);
        //////    //SqlCommand sqlCommd = new SqlCommand();

        //////    //try
        //////    //{
        //////    //    sqlCommd.CommandType = CommandType.StoredProcedure;
        //////    //    sqlCommd.CommandText = storedProcName;

        //////    //    if (dsProcParams.Tables.Count > 0)
        //////    //    {
        //////    //        PopulateSQLCommandFromDataTable(sqlCommd, dsProcParams.Tables[0]);
        //////    //    }

        //////    //    return DBExecuteDataSet(sqlCommd);
        //////    //}
        //////    //finally
        //////    //{
        //////    //    sqlCommd.Dispose();
        //////    //}
        //////}

        #endregion

        #region ExecuteNonQuery
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and returns the number of rows affected.
        /// </summary>
        /// <param name="sqlCommand">The SqlCommand object to use.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
        public int DBExecuteNonQuery(SqlCommand sqlCommand)
        {
            return base.DBExecuteNonQuery(sqlCommand);
        }

        public int DBSQLExecuteNonQuery(SqlCommand sqlCommand)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            try
            {
                return db.ExecuteNonQuery(sqlCommand);
            }
            finally
            {
                if (sqlCommand.Connection.State != ConnectionState.Closed) { sqlCommand.Connection.Close(); }
            }
        }
        #endregion

        #region ExecuteScalar        
        
        #endregion

        #region DataReader
        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// 
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns></returns>
        /////////----------------------------------------------------------------------------------
        //////public  SqlDataReader DBExecuteReader(string storedProcedureName, params object[] parameterValues)
        //////{
        //////    return (SqlDataReader)base.DBExecuteReader(storedProcedureName, parameterValues); 
        //////        //(SqlDataReader)DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName, parameterValues);
        //////}
        public SqlDataReader DBSQLExecuteReader(string storedProcedureName, params object[] parameterValues)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            return (SqlDataReader)db.ExecuteReader(storedProcedureName, parameterValues);
        }
        #endregion

        #region Misc
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Wrapper method to return a StoredProcCommand.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>SqlCommand</returns>
        ///----------------------------------------------------------------------------------
        public SqlCommand DBGetStoredProcSqlCommand(string storedProcedureName)
        {
            return (SqlCommand)base.DBGetStoredProcCommand(storedProcedureName);
        }

        public SqlCommand DBSQLGetStoredProcSqlCommand(string storedProcedureName)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            return (SqlCommand)db.GetStoredProcCommand(storedProcedureName);
        }

        public IDbCommand DBSQLGetStoredProcCommand(string storedProcedureName)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            return db.GetStoredProcCommand(storedProcedureName);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Wrapper method to return a StoredProcCommand.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues"></param>
        /// <returns>SqlCommand</returns>
        ///----------------------------------------------------------------------------------
        public SqlCommand DBGetStoredProcSqlCommand(string storedProcedureName, params object[] parameterValues)
        {
            return (SqlCommand)base.DBGetStoredProcCommand(storedProcedureName, parameterValues);
        }

        public SqlCommand DBSQLGetStoredProcSqlCommand(string storedProcedureName, params object[] parameterValues)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            return (SqlCommand)db.GetStoredProcCommand(storedProcedureName, parameterValues);
        }

        public IDbCommand DBSQLGetStoredProcCommand(string storedProcedureName, params object[] parameterValues)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            return db.GetStoredProcCommand(storedProcedureName, parameterValues);
        }

        public bool DBExecuteNonQuerybool(string storedProcedureName, params object[] parameterValues)
        {
            SqlCommand dbCommand = (SqlCommand)base.DBGetStoredProcCommand(storedProcedureName);
            try
            {
                return base.DBExecuteNonQuery(storedProcedureName, parameterValues) == 0 ? false : true;
            }
            finally
            {
                if (dbCommand.Connection.State != ConnectionState.Closed) { dbCommand.Connection.Close(); }
                dbCommand.Dispose();
            }
        }

        public bool DBSQLExecuteNonQuerybool(string storedProcedureName, params object[] parameterValues)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(storedProcedureName);
            try
            {
                return db.ExecuteNonQuery(storedProcedureName, parameterValues) == 0 ? false : true;
            }
            finally
            {
                if (dbCommand.Connection.State != ConnectionState.Closed) { dbCommand.Connection.Close(); }
                dbCommand.Dispose();
                if (db != null) db = null;
            }
        }

        public int DBExecuteNonQueryReturnValue(string storedProcedureName, params object[] parameterValues)
        {

            SqlCommand dbCmd = (SqlCommand)base.DBGetStoredProcCommand(storedProcedureName, parameterValues);

            try
            {
                return (int)base.DBExecuteScalar(dbCmd);
            }
            finally
            {
                dbCmd.Connection.Close();
                dbCmd.Dispose();
            }
        }

        public int DBSQLExecuteNonQueryReturnValue(string storedProcedureName, params object[] parameterValues)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);

            SqlCommand dbCmd = (SqlCommand)db.GetStoredProcCommand(storedProcedureName, parameterValues);

            try
            {
                return (int)db.ExecuteScalar(dbCmd);
            }
            finally
            {
                dbCmd.Connection.Close();
                dbCmd.Dispose();
                if (db != null) db = null;
            }
        }

        public void DBAddInParameter(SqlCommand sqlCommand, string name, DbType dbType)
        {
            base.DBAddInParameter(sqlCommand, name, dbType);
        }

        public void DBSQLAddInParameter(SqlCommand sqlCommand, string name, DbType dbType)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);

            try
            {
                db.AddInParameter(sqlCommand, name, dbType);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        public void DBAddInParameter(SqlCommand sqlCommand, string name, DbType dbType, object value)
        {
                base.DBAddInParameter(sqlCommand, name, dbType, value);
        }

        public void DBSQLAddInParameter(SqlCommand sqlCommand, string name, DbType dbType, object value)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);

            try
            {
                db.AddInParameter(sqlCommand, name, dbType, value);
            }
            finally
            {
                if (db != null) db = null;
            }
        }

        public void DBAddOutParameter(SqlCommand sqlCommand, string name, DbType dbType, int size)
        {
            base.DBAddOutParameter(sqlCommand, name, dbType, size);
        }

        public void DBSQLAddOutParameter(SqlCommand sqlCommand, string name, DbType dbType, int size)
        {
            SqlDatabase db = new SqlDatabase(this.ConnectionString);

            try
            {
                db.AddOutParameter(sqlCommand, name, dbType, size);
            }
            finally
            {
                if (db != null) db = null;
            }
        }
        #endregion

        #endregion

        #region Static Methods

        #region DataReader
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure and returns the results as a SqlDataReader.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>Result as a SqlDataReader.</returns>
        ///----------------------------------------------------------------------------------
        public static SqlDataReader ExecuteSqlReader(string storedProcedureName)
        {
            return (SqlDataReader)DatabaseFactory.CreateDatabase().ExecuteReader(storedProcedureName);
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with parameterValues and returns the results as a SqlDataReader.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>Result as a SqlDataReader</returns>
        ///----------------------------------------------------------------------------------
        public static SqlDataReader ExecuteSqlReader(string storedProcedureName, params object[] parameterValues)
        {
            return (SqlDataReader)DatabaseFactory.CreateDatabase().ExecuteReader(storedProcedureName, parameterValues);
        }
        #endregion

        #region DataSet
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and returns the results as a DataSet.
        /// </summary>
        /// <param name="sqlCommand">The SqlCommand object to use.</param>
        /// <returns>Result as a DataSet.</returns>
        ///----------------------------------------------------------------------------------
        public static DataSet ExecuteDataSet(SqlCommand sqlCommand)
        {
            Database db = DatabaseFactory.CreateDatabase();

            try
            {
                DataSet ds = db.ExecuteDataSet(sqlCommand);
                return ds;
            }
            finally
            {
                sqlCommand.Connection.Close();
                sqlCommand.Dispose();
                if (db != null) db = null;
            }
        }
        #endregion

        #region NonQuery
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and returns the number of rows affected.
        /// </summary>
        /// <param name="sqlCommand">The SqlCommand object to use.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
        public static int ExecuteNonQuery(SqlCommand sqlCommand)
        {
            Database db = DatabaseFactory.CreateDatabase();
            try
            {
                int rows = db.ExecuteNonQuery(sqlCommand);
                return rows;
            }
            finally
            {
                sqlCommand.Connection.Close();
                sqlCommand.Dispose();
                if (db != null) db = null;
            }
        }
        #endregion

        #region ExecuteScalar
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and returns a single result as an object.
        /// </summary>
        /// <param name="sqlCommand">The SqlCommand object to use.</param>
        /// <returns>object</returns>
        ///----------------------------------------------------------------------------------
        public static object ExecuteScalar(SqlCommand sqlCommand)
        {
            Database db;
            db = DatabaseFactory.CreateDatabase();
            try
            {
                return db.ExecuteScalar(sqlCommand);
            }
            finally
            {
                sqlCommand.Connection.Close();
                sqlCommand.Dispose();
                if (db != null) db = null;
            }
        }
        #endregion

        #region Misc
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pSQLCommand"></param>
        /// <param name="pDataTable"></param>
        ///----------------------------------------------------------------------------------
        public static void PopulateSQLCommandFromDataTable(SqlCommand pSQLCommand, DataTable pDataTable)
        {
            if (pDataTable.Rows.Count > 0)
            {
                PopulateSQLCommandFromDataRow(pSQLCommand, pDataTable.Columns, pDataTable.Rows[0]);
            }
        }

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pSQLCommand"></param>
        /// <param name="pColCollection"></param>
        /// <param name="pDatarow"></param>
        ///----------------------------------------------------------------------------------
        public static void PopulateSQLCommandFromDataRow(SqlCommand pSQLCommand, DataColumnCollection pColCollection, DataRow pDatarow)
        {
            for (int i = 0; i < pColCollection.Count; i++)
            {
                pSQLCommand.Parameters.AddWithValue("@" + pColCollection[i].ColumnName, pDatarow.ItemArray[i]);
            }
        }
        #endregion

        #endregion
    }
}
